package admin.statisticsToExcel;

import java.io.File;

import jxl.CellView;
import jxl.Workbook;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import org.apache.struts2.ServletActionContext;

import admin.service.member.LeaderServiceAdmin;
import teacher.bean.Teacher;

public class AwardToExcel {

	
	public static void awardExcel()
	{

        try {
            WritableWorkbook wwb = null; 
            String uploadPath = ServletActionContext.getServletContext()
					.getRealPath("/upload");
            
            File dir=new File(uploadPath);  
	       //如果这个目录不存在,则创建它  
	        if (!dir.exists()){  
	            dir.mkdir();  
	        } 
               // 创建可写入的Excel工作簿
             String fileName = uploadPath+"/"+"awardCount.xls";
              File file=new File(fileName);
               if (!file.exists()){
                   file.createNewFile();
               }
               //以fileName为文件名来创建一个Workbook
               wwb = Workbook.createWorkbook(file);
               // 创建工作表
               WritableSheet ws = wwb.createSheet("Test Shee 1", 0);   
               
               
               //合并单元格  生成标题  控制标题样式
               WritableFont bold = new WritableFont(WritableFont.ARIAL,12,WritableFont.BOLD);//设置字体种类和黑体显示,字体为Arial,字号大小为10,采用黑体显示
               WritableCellFormat titleFormate = new WritableCellFormat(bold);//生成一个单元格样式控制对象
               titleFormate.setAlignment(jxl.format.Alignment.CENTRE);//单元格中的内容水平方向居中
               titleFormate.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);//单元格的内容垂直方向居中
               Label title = new Label(0,0,"信息工程学院 控制科学与工程学科 获奖统计",titleFormate);
               ws.setRowView(0, 600, false);//设置第一行的高度
               ws.addCell(title);
            /*   //设置居中
             WritableCellFormat wf=  new WritableCellFormat();
             wf.setAlignment(jxl.format.Alignment.CENTRE);//单元格中的内容水平方向居中
*/               //合并单元格标题
               ws.mergeCells(0, 0, 5,0);
               //设置列宽
               ws.setColumnView(1, 12);
               ws.setColumnView(2, 12);
               ws.setColumnView(3, 12);
               ws.setColumnView(4, 12);
              
               //设置字体颜色
               WritableFont color = new WritableFont(WritableFont.ARIAL);//选择字体
               color.setColour(Colour.BLUE);//设置字体颜色为金黄色
               WritableCellFormat colorFormat0 = new WritableCellFormat(color);
               
               //设置单元格格式
               WritableCellFormat cellFormat = new WritableCellFormat();
               cellFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
               //设置列宽 自适应
               CellView cellView = new CellView(); 
               cellView.setAutosize(true); //设置自动大小    
               //设置列宽
               ws.setColumnView(0, 20);
             
               //要插入到的Excel表格的行号，默认从0开始
               Label aid= new Label(0, 1, "方向名称");//表示第
               Label aname= new Label(1,1, "国家级数量");//表示第
               Label aprojectName= new Label(2, 1, "省部级数量");
               Label aprojectName1= new Label(3, 1, "厅局级数量");
               Label alevel= new Label(4, 1, "校级数量");   
               Label apeople= new Label(5, 1, "总数量");
               
             //  aid.setCellFormat(wf);           
         
               ws.addCell(aid);
               ws.addCell(aname);
               ws.addCell(aprojectName);
               ws.addCell(aprojectName1);
               ws.addCell(alevel);
               ws.addCell(apeople);
              
               
               LeaderServiceAdmin ls=new LeaderServiceAdmin();
 
               for (int i = 0; i < 4; i++) 
               {  
            	  Teacher tea =new Teacher();
            	  tea.setDirection(i);
            	   Label aaid= new Label(1, i+2, ls.getCountOfAwardInclude(1, tea)+"");
                   Label aaname= new Label(2, i+2, ls.getCountOfAwardInclude(2, tea)+"");
                   Label aaprojectName= new Label(3, i+2,ls.getCountOfAwardInclude(3, tea)+"");
                   Label aaprojectName1= new Label(4, i+2,ls.getCountOfAwardInclude(4, tea)+"");
                   Label aalevel= new Label(5, i+2, ls.getCountOfDirectAward(tea)+""); 
                  
                	              
                   ws.addCell(aaid);
                   ws.addCell(aaname);
                   ws.addCell(aaprojectName);
                   ws.addCell(aaprojectName1);
                   ws.addCell(aalevel); 
                   
          
                 
               }  
               Label id1= new Label(0, 2, "控制理论与控制工程 "+"");
               Label id2= new Label(0, 3,"机器人系统与智能装备 "+"");
               Label id3= new Label(0, 4, "模式识别与智能系统 "+"");
               Label id4= new Label(0, 5, "综合自动化与智能交通 "+"");
               
               
               id1.setCellFormat(colorFormat0);
               id2.setCellFormat(colorFormat0);
               id3.setCellFormat(colorFormat0);
               id4.setCellFormat(colorFormat0);
          
               ws.addCell(id1);
               ws.addCell(id2);
               ws.addCell(id3);
               ws.addCell(id4);
               
              //写进文档
               wwb.write();
              // 关闭Excel工作簿对象
               wwb.close();     
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } 
    }
}
